﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NikeSale.Dal;
using System.Data.SqlClient;
using NikeSale.Models;
using System.Windows.Forms;
using System.Data;

namespace NikeSale.Bll
{
    /// <summary>
    /// 主要逻辑类
    /// </summary>
    public static class DBSelect
    {
        /// <summary>
        /// Guide名称数组
        /// </summary>
        public static string[] Guides { get { return GetGuides(); } }

        /// <summary>
        /// 从数据库更新Guide信息
        /// </summary>
        /// <returns></returns>
        private static string[] GetGuides()
        {
            string sql = "select man.SalesmanName,man.SalesmanID from [dbo].[Salesman] man\n" +
            "where man.Role = '导购员'";
            List<string> list = new List<string>();
            SqlDataReader rd = DBHelper.GetReader(sql);
            while (rd.Read())
            {
                list.Add(rd["SalesmanID"].ToString().PadLeft(4, '0') + "-" + rd["SalesmanName"].ToString());
            }
            rd.Close();
            return list.ToArray();
        }

        /// <summary>
        /// 登陆类
        /// </summary>
        public static class Login
        {
            /// <summary>
            /// 标识着登陆判断结果
            /// </summary>
            public enum LoginErrors
            {
                NotError = 0,
                UserNameError = 1,
                PassWordError = 2
            }

            /// <summary>
            /// 对比账号密码信息,并返回判断结果
            /// </summary>
            /// <param name="mob">登陆账号(电话号码)</param>
            /// <param name="pwd">登陆密码</param>
            /// <param name="pos">登陆身份</param>
            public static LoginErrors UserSelect(string mob, string pwd)
            {
                //检测用户名是否存在
                string sql = $"SELECT COUNT(*) FROM [dbo].[Salesman] WHERE Mobile='{mob}'";
                if ((int)DBHelper.ExecuteScalar(sql) == 0) return LoginErrors.UserNameError;

                //检测密码是否正确
                sql = $"SELECT COUNT(*) FROM [dbo].[Salesman] WHERE Mobile='{mob}' AND Pwd collate Chinese_PRC_CS_AS ='{pwd}'";
                if ((int)DBHelper.ExecuteScalar(sql) == 0) return LoginErrors.PassWordError;
                else return LoginErrors.NotError;
            }

            /// <summary>
            /// 返回登陆用户信息
            /// </summary>
            public static LoginInfo GetLoginInfo(string mob, string pwd)
            {
                string sql = $"SELECT SalesmanID,Mobile,SalesmanName,Role,Gender FROM [dbo].[Salesman] " +
                     $"WHERE Mobile='{mob}' AND Pwd='{pwd}'";

                SqlDataReader rd = DBHelper.GetReader(sql);
                rd.Read();
                LoginInfo info = new LoginInfo()
                {
                    UserID = rd["SalesmanID"].ToString(),
                    Role= (Positions)Enum.Parse(typeof(Positions),rd["Role"].ToString()),
                    LoginID = rd["Mobile"].ToString(),
                    UserName = rd["SalesmanName"].ToString(),
                    Gender = rd["Gender"].ToString()
                };
                rd.Close();
                return info;
            }

            /// <summary>
            /// 检测Sql服务是否打开
            /// </summary>
            /// <returns></returns>
            public static bool SqlserviceIsOpen()
            {
                return DBHelper.SqlServiceIsOpen();
            }
        }

        /// <summary>
        /// GoodsBrowse模块
        /// </summary>
        public static class GoodsBrowse
        {
            /// <summary>
            /// 根据条件Query所有商品信息
            /// </summary>
            /// <param name="barCode">货号</param>
            /// <param name="name">商品名称</param>
            /// <param name="type1">一级分类</param>
            /// <param name="type2">二级分类</param>
            /// <param name="start">入库时间区间开始</param>
            /// <param name="end">入库时间区间结束</param>
            /// <returns>返回所有列</returns>
            public static List<ListViewItem> GoodsSelect(DateTime start, DateTime end, ComboxItem parentType, ComboxItem subType, string barCode, string name)
            {
                string sql = $"select * from [dbo].[Goods] gs join [dbo].[Type] ty " +
                    $"on gs.TypeID=ty.TypeID " +
                    $"where (gs.StockDate between '{start.ToString("yyyy-M-d 00:00:00")}' and '{end.ToString("yyyy-M-d 23:59:59")}') ";

                #region 附加条件补充
                //货号判断
                if (barCode != "全部")
                {
                    sql += $"and (gs.BarCode='{barCode}') ";
                }
                //商品名判断
                if (name != "全部")
                {
                    sql += $"and (gs.GoodsName='{name}') ";
                }
                //商品类型判断
                if (parentType.Text != "全部")
                {
                    if (subType.Text != "全部")
                        sql += $"and (ty.ParentID={parentType.ID} and ty.TypeName='{subType.Text}')";
                    else
                    {
                        sql += $"and (ty.ParentID ={parentType.ID})";
                    }
                }
                #endregion
                SqlDataReader rd = DBHelper.GetReader(sql);
                List<ListViewItem> list = new List<ListViewItem>();
                for (int i = 1; rd.Read(); i++)
                {
                    ListViewItem item = new ListViewItem(i.ToString());
                    item.SubItems.Add(rd["GoodsID"].ToString());
                    item.SubItems.Add(rd["BarCode"].ToString());
                    item.SubItems.Add(rd["GoodsName"].ToString());
                    item.SubItems.Add(rd["TypeName"].ToString());
                    item.SubItems.Add(rd["StorePrice"].ToString());
                    item.SubItems.Add(rd["SalePrice"].ToString());
                    item.SubItems.Add(rd["Discount"].ToString());
                    item.SubItems.Add(rd["StockNum"].ToString());
                    item.SubItems.Add(rd["StockDate"].ToString());
                    list.Add(item);
                }
                rd.Close();
                return list;
            }
        }

        /// <summary>
        /// 销售记录模块
        /// </summary>
        public static class SalesQuery
        {
            /// <summary>
            /// 从数据库获取销售信息表
            /// </summary>
            /// <param name="guide"></param>
            /// <param name="condValue"></param>
            /// <param name="start"></param>
            /// <param name="end"></param>
            /// <returns></returns>
            public static List<ListViewItem> SalesRecordSelect(string guide, DateTime start, DateTime end)
            {
                string sql =
                    $"select sal.ReceiptsCode,\n" +//流水号
                    $"sal.SalesDate,\n" +          //销售日期
                    $"sal.Amount,\n" +             //购物Amount
                    $"sal.Profit,\n" +             //单笔利润
                    $"man1.SalesmanName,\n" +                                     //经手的Guide
                    $"man2.SalesmanName as CashierName \n" +                      //经手的收银员
                    $"from [dbo].[Sales] sal " +
                    $"join [dbo].[Salesman] man1 on man1.SalesmanID=sal.SalesmanID \n" +
                    $"join [dbo].[Salesman] man2 on man2.SalesmanID=sal.CashierID \n" +
                   $"where (sal.SalesDate between '{start.ToString("yyyy-M-d 00:00:00")}' and '{end.ToString("yyyy-M-d 23:59:59")}') ";
                if (guide != "全部")
                {
                    sql += $"and (sal.SalesmanId= {guide.Split('-')[0]}) ";
                }
                SqlDataReader rd = DBHelper.GetReader(sql);
                List<ListViewItem> list = new List<ListViewItem>();
                for (int i = 1; rd.Read(); i++)
                {
                    ListViewItem item = new ListViewItem(i.ToString());
                    item.SubItems.Add(rd["ReceiptsCode"].ToString());
                    item.SubItems.Add(rd["SalesDate"].ToString());
                    item.SubItems.Add(rd["Amount"].ToString());
                    item.SubItems.Add(rd["Profit"].ToString());
                    item.SubItems.Add(rd["SalesmanName"].ToString());
                    item.SubItems.Add(rd["CashierName"].ToString());
                    list.Add(item);
                }
                rd.Close();
                return list;
            }
        }

        /// <summary>
        /// 收银台模块
        /// </summary>
        public static class Cashier
        {
            /// <summary>
            /// 根据货号返回商品信息
            /// </summary>
            /// <param name="GoodsID"></param>
            /// <returns></returns>
            public static Goods GetGoods(string GoodsID)
            {
                string sql = $"select * from [dbo].[Goods] gds " +
                    $" join [dbo].[Type] ty on gds.TypeID=ty.TypeID " +
                    $"where gds.BarCode='{GoodsID}'";
                SqlDataReader rd = DBHelper.GetReader(sql);
                if (!rd.HasRows)
                {
                    rd.Close();
                    return null;
                }
                Goods gds = new Goods();
                rd.Read();
                gds.GoodsID = rd["GoodsID"].ToString();
                gds.BarCode = rd["BarCode"].ToString();
                gds.StorePrice = rd["StorePrice"].ToString();
                gds.SalePrice = rd["SalePrice"].ToString();
                gds.StockNum = rd["StockNum"].ToString();
                gds.GoodsName = rd["GoodsName"].ToString();
                gds.Discount = rd["Discount"].ToString();
                gds.StockDate = Convert.ToDateTime(rd["StockDate"].ToString());
                gds.GoodsTypeName = rd["TypeName"].ToString();
                rd.Close();
                return gds;
            }

            /// <summary>
            /// 向销售记录表中插入一条数据
            /// </summary>
            /// <param name="sales"></param>
            public static void InsertSalesRecord(Sales sales)
            {
                string sql = $"insert into [dbo].[Sales]\n" +
                    $"values('{sales.ReceiptsCode}','{sales.SalesDate.ToString()}',{sales.Amount},{sales.SalesmanID},{sales.CashierID},{sales.Profit})";
                if (!DBHelper.ExecuteNonQuery(sql))
                {
                    throw new Exception("数据库数据插入失败");
                }
            }

            /// <summary>
            /// 返回销售记录表中最后一条的ID
            /// </summary>
            /// <param name="SerialNumber">销售流水号</param>
            /// <returns></returns>
            public static string ReturnFinalSalesID(string SerialNumber)
            {
                string sql = $"select SalesID  from [dbo].[Sales] sa\n" +
                    $"where sa.ReceiptsCode='{SerialNumber}'";
                return DBHelper.ExecuteScalar(sql).ToString();
            }

            /// <summary>
            /// 向销售明细表中插入一条数据
            /// </summary>
            /// <param name="det"></param>
            public static void InsertSalesDetail(SalesDetail det)
            {
                string sql = $"insert into [dbo].[SalesDetail]\n" +
                    $"values({det.SalesID},{det.GoodsID},{det.Quantity},{det.AloneAmount})";

                if (!DBHelper.ExecuteNonQuery(sql))
                {
                    throw new Exception("数据插入失败");
                }
            }

            /// <summary>
            /// 更新仓库商品库存数
            /// </summary>
            /// <param name="GoodsID">商品ID</param>
            /// <param name="num">销售的数量</param>
            /// <returns>如果返回false 则说明库存不够</returns>
            public static void UpdateGoodsQuantity(string GoodsID, int num)
            {
                string sql = $"update [dbo].[Goods] set StockNum=StockNum-{num}\n" +
                   $"where GoodsID={GoodsID}";
                if (!DBHelper.ExecuteNonQuery(sql))
                {
                    throw new Exception("库存更新错误!");
                }
            }

            /// <summary>
            /// 比较指定商品库存是否大于或等于指定数
            /// </summary>
            /// <param name="GoodsID">商品ID</param>
            /// <param name="num">要比较的数值</param>
            /// <returns>true :大于等于num</returns>
            public static bool GoodsQuantityCompare(string GoodsID, int num)
            {
                string sql = "select gs.StockNum from [dbo].[Goods] gs\n" +
                   $"where gs.GoodsID={GoodsID}";

                int value = int.Parse(DBHelper.ExecuteScalar(sql).ToString());
                return value >= num;
            }
        }

        /// <summary>
        /// 工资核算模块
        /// </summary>
        public static class WageAccount
        {
            /// <summary>
            /// 返回对应的员工信息
            /// </summary>
            /// <param name="start"></param>
            /// <param name="end"></param>
            /// <returns></returns>
            public static List<ListViewItem> SelectAllSalesman(DateTime start, DateTime end)
            {
                string sql = "select\n" +
                    "a.SalesmanName," +
                    "a.Role," +
                    "a.Mobile," +
                    "a.BaseSalary," +
                    "a.CommissionRate," +
                    "ISNULL(b.volume,0) Volume," +
                    "a.BaseSalary+ISNULL(a.CommissionRate*b.volume,0) Wages," +
                    "b.SalesDate\n" +
                    "from(\n" +
                    "select\n" +
                    "man.SalesmanID," +
                    "man.SalesmanName," +
                    "man.Role," +
                    "man.Mobile," +
                    "man.BaseSalary," +
                    "man.CommissionRate\n" +
                    "from [dbo].[Salesman] man) a\n" +
                    "full join (\n" +
                    "select\n" +
                    "man.SalesmanID id," +
                    "SUM(sal.Amount) volume," +
                    "sal.SalesDate\n" +
                    "from [dbo].[Salesman] man \n" +
                    "inner join [dbo].[Sales] sal\n" +
                    "on sal.SalesmanID=man.SalesmanID or sal.CashierID=man.SalesmanID\n" +
                    "group by man.SalesmanID,sal.SalesDate\n" +
                    $"having sal.SalesDate between '{start.ToString("yyyy-M-d 00:00:00")}' and '{end.ToString("yyyy-M-d 23:59:59")}'\n" +
                    ") b\n" +
                    "on a.SalesmanID=b.id\n" +
                    "where b.SalesDate is not null";

                SqlDataReader dr = DBHelper.GetReader(sql);
                List<ListViewItem> list = new List<ListViewItem>();
                for (int i = 1; dr.Read(); i++)
                {
                    ListViewItem item = new ListViewItem(i.ToString());
                    item.SubItems.Add(dr["SalesmanName"].ToString());
                    item.SubItems.Add(dr["Role"].ToString());
                    item.SubItems.Add(dr["Mobile"].ToString());
                    item.SubItems.Add(dr["BaseSalary"].ToString());
                    item.SubItems.Add(dr["CommissionRate"].ToString());
                    item.SubItems.Add(dr["Volume"].ToString());
                    item.SubItems.Add(dr["Wages"].ToString());
                    list.Add(item);
                }
                dr.Close();
                return list;
            }
        }

        /// <summary>
        /// 商品分类
        /// </summary>
        public static class GoodsClassIfication
        {
            private static string tablename = "DboTypes";

            private static string sqlText = "select TypeID,TypeName,ParentID from [dbo].[Type]";

            /// <summary>
            /// 返回所有分类数据
            /// </summary>
            /// <returns></returns>
            public static DataTable SelectAllTypes()
            {
                //string sql = "select TypeID,TypeName,ParentID from [dbo].[Type]";
                DataTable table = DBHelper.GetTable(sqlText, tablename);
                table.PrimaryKey = new DataColumn[] { table.Columns["TypeID"] };
                return table;
            }

            /// <summary>
            /// 将修改后的数据更新至数据库
            /// </summary>
            public static int UpdateTable()
            {
                return DBHelper.DataSetUpdate(tablename, sqlText);
            }

            /// <summary>
            /// 检索数据库中是否包含该分类名
            /// </summary>
            /// <param name="typeName"></param>
            /// <returns></returns>
            public static bool HasTypeName(string typeName)
            {
                string sql = $"select COUNT(*) from [dbo].[Type] where TypeName='{typeName}'";
                return DBHelper.ExecuteScalar(sql).ToString() != "0";
            }

            /// <summary>
            /// 更新分类信息
            /// </summary>
            /// <param name="typeID"></param>
            /// <param name="newName"></param>
            public static void UpdateTypeInfo(string typeID, string newName)
            {
                string sql = $"update [dbo].[Type] set TypeName='{newName}'\n" +
                    $"where TypeID={typeID}";
                if (!DBHelper.ExecuteNonQuery(sql))
                {
                    throw new Exception("数据更新失败");
                }
            }

            /// <summary>
            /// 插入分类信息
            /// </summary>
            /// <param name="typeName"></param>
            /// <param name="praentName"></param>
            public static void InsertTypeInfo(string typeName, string parentId)
            {
                string sql = "insert into [dbo].[Type] \n" +
                     $"values('{typeName}',{parentId})";
                if (!DBHelper.ExecuteNonQuery(sql))
                {
                    throw new Exception("插入分类信息时错误");
                }
            }

            /// <summary>
            /// 获取父类相关的所有子类
            /// </summary>
            /// <param name="parentType">父类ID或者名称</param>
            /// <returns></returns>
            public static ComboxItem[] GetSubTypes(string parentTypeName)
            {
                string sql = "select t2.TypeID,t2.TypeName \n" +
                     "from [dbo].[Type] t1\n" +
                     "join [dbo].[Type] t2 on t2.ParentID=t1.TypeID\n" +
                     $"where t1.TypeName='{parentTypeName}'";
                List<ComboxItem> list = new List<ComboxItem>();
                SqlDataReader rd = DBHelper.GetReader(sql);
                while (rd.Read())
                {
                    ComboxItem item = new ComboxItem();
                    item.Text = rd["TypeName"].ToString();
                    item.ID = rd["TypeID"].ToString();
                    list.Add(item);
                }
                rd.Close();
                return list.ToArray();
            }

            /// <summary>
            /// 获取所有父类以及对应ID
            /// </summary>
            /// <returns></returns>
            public static ComboxItem[] GetParentTypes()
            {
                string sql = "select TypeID,TypeName from [dbo].[Type] t1\n" +
                    "where t1.ParentID = 0";
                List<ComboxItem> list = new List<ComboxItem>();
                SqlDataReader rd = DBHelper.GetReader(sql);
                while (rd.Read())
                {
                    ComboxItem item = new ComboxItem();
                    item.Text = rd["TypeName"].ToString();
                    item.ID = rd["TypeID"].ToString();
                    list.Add(item);
                }
                rd.Close();
                return list.ToArray();
            }

            /// <summary>
            /// 获取指定子类的父级ID
            /// </summary>
            /// <param name="subType">子类名或者ID</param>
            /// <returns></returns>
            public static string GetParentTypeID(string subTypeName)
            {
                string sql = "select ParentID from [dbo].[Type] t1\n" +
                    $"where t1.TypeName='{subTypeName}'";
                return DBHelper.ExecuteScalar(sql).ToString();
            }
        }

        /// <summary>
        /// 员工管理模块
        /// </summary>
        public static class EmployeeManagement
        {
            private static string sql =
                "select SalesmanID,SalesmanName,Mobile,Gender,BaseSalary,CommissionRate,Role from [dbo].[Salesman]";

            private static string tableName = "Salesman";

            /// <summary>
            /// 返回所有员工信息
            /// </summary>
            /// <returns></returns>
            public static DataTable SelectALLSalesman()
            {
                DataTable tab = DBHelper.GetTable(sql, tableName);
                tab.PrimaryKey = new DataColumn[] { tab.Columns["SalesmanID"] };
                return tab;
            }

            /// <summary>
            /// 更新所有改动数据
            /// </summary>
            /// <returns></returns>
            public static int UpdateSalesmanInfo()
            {
                return DBHelper.DataSetUpdate(tableName, sql);
            }
        }

        /// <summary>
        /// 商品入库模块
        /// </summary>
        public static class GoodsWarehousing
        {
            /// <summary>
            /// 根据货号返回商品信息
            /// </summary>
            /// <param name="GoodsID">货号</param>
            /// <returns></returns>
            public static Goods GetGoodsInfo(string GoodsID)
            {
                string sql = $"select * from [dbo].[Goods] gds " +
                    $" join [dbo].[Type] ty on gds.TypeID=ty.TypeID " +
                    $"where gds.BarCode='{GoodsID}'";
                SqlDataReader rd = DBHelper.GetReader(sql);
                if (!rd.HasRows)
                {
                    rd.Close();
                    return null;
                }
                Goods gds = new Goods();
                rd.Read();
                gds.GoodsID = rd["GoodsID"].ToString();
                gds.BarCode = rd["BarCode"].ToString();
                gds.StorePrice = rd["StorePrice"].ToString();
                gds.SalePrice = rd["SalePrice"].ToString();
                gds.StockNum = rd["StockNum"].ToString();
                gds.GoodsName = rd["GoodsName"].ToString();
                gds.Discount = rd["Discount"].ToString();
                gds.StockDate = Convert.ToDateTime(rd["StockDate"].ToString());
                gds.GoodsTypeName = rd["TypeName"].ToString();
                rd.Close();
                return gds;
            }

            /// <summary>
            /// 检测数据库中是否包含指定商品ID
            /// </summary>
            /// <param name="GoodsID">商品货号</param>
            /// <returns>True 存在改数据</returns>
            public static bool HasGoods(string code)
            {
                string sql = $"select COUNT(*) from [dbo].[Goods] where BarCode={code}";
                return DBHelper.ExecuteScalar(sql).ToString() != "0";
            }

            /// <summary>
            /// 添加库存数量
            /// </summary>
            /// <param name="num"></param>
            public static void AddStockNum(string num, string barCode)
            {
                string sql = $"update [dbo].[Goods] set StockNum=StockNum+{num}\n" +
                    $"where BarCode='{barCode}'";

                DBHelper.ExecuteNonQuery(sql);
            }

            /// <summary>
            /// 返回指定货号的商品库存
            /// </summary>
            /// <param name="barCode">货号</param>
            /// <returns></returns>
            public static string GetStockNum(string barCode)
            {
                string sql = $"select StockNum from [dbo].[Goods] where BarCode='{barCode}'";

                return DBHelper.ExecuteScalar(sql).ToString();
            }

            /// <summary>
            /// 插入新的商品
            /// </summary>
            /// <param name="gs"></param>
            public static void InsertGoods(Goods gs)
            {
                string sql = $"insert into [dbo].[Goods]\n" +
                    $"values('{gs.BarCode}',{gs.GoodsTypeID},'{gs.GoodsName}',{gs.StorePrice},{gs.SalePrice},{gs.Discount},{gs.StockNum},'{gs.StockDate.ToString()}')";
                DBHelper.ExecuteNonQuery(sql);
            }
        }

        /// <summary>
        /// 退货模块
        /// </summary>
        public static class ReturnGoods
        {
            private static string TableName = "RetGoods";

            /// <summary>
            /// 判断是否存在该流水号
            /// </summary>
            /// <param name="Number">判断的流水号</param>
            /// <returns></returns>
            public static bool HasReceiptsCode(string number)
            {
                string sql = $"select COUNT(*) from [dbo].[Sales] where ReceiptsCode='{number}'";

                return DBHelper.ExecuteScalar(sql).ToString() != "0";
            }

            /// <summary>
            /// 返回退货明细表Query信息
            /// </summary>
            /// <param name="receCode">流水号</param>
            /// <returns></returns>
            public static DataTable QueryData(string receCode)
            {
                string sql = "select\n" +
                             "sd.SDID," +
                             "sa.ReceiptsCode," +
                             "gs.GoodsName," +
                             "sd.AloneAmount," +
                             "sd.Quantity," +
                             "man.SalesmanName," +
                             "sa.SalesDate\n" +
                             "from[dbo].[Sales] sa\n" +
                             "join[dbo].[SalesDetail] sd on sa.SalesID=sd.SalesID\n" +
                             "join[dbo].[Salesman] man on sa.SalesmanID=man.SalesmanID\n" +
                             "join[dbo].[Goods] gs on sd.GoodsID=gs.GoodsID\n" +
                             $"where sa.ReceiptsCode='{receCode}'";
                return DBHelper.GetTable(sql, TableName);
            }

            /// <summary>
            /// 退货处理,并更新销售记录总利润Amount
            /// </summary>
            /// <param name="sdid">销售明细ID</param>
            /// <param name="refPrice">退货Amount</param>
            public static void DeleteSalesDetail(string sdid,string receCode)
            {
                //更新利润Amount
                double pro = GetProfit(sdid);
                string sql = $"update [dbo].[Sales] set Profit=Profit-{pro.ToString()}\n" +
                    $"where ReceiptsCode={receCode}";
                DBHelper.ExecuteNonQuery(sql);
                //更新交易Amount
                
                //删除相关明细记录
                sql = $"delete [dbo].[SalesDetail] where SDID={sdid}";
                DBHelper.ExecuteNonQuery(sql);
            }

            /// <summary>
            /// 更新销售记录总交易Amount
            /// </summary>
            /// <param name="refPrice"></param>
            /// <param name="receCode"></param>
            public static void UpdateSales(string refPrice, string receCode)
            {
                string sql = $"update [dbo].[Sales] set Amount=Amount-{refPrice}\n" +
                    $"where ReceiptsCode={receCode}";
                DBHelper.ExecuteNonQuery(sql);
            }

            /// <summary>
            /// 删除该条销售记录
            /// </summary>
            /// <param name="recrCode"></param>
            public static void ClearSales(string recrCode)
            {
                string sql = $"delete from [dbo].[Sales] where ReceiptsCode='{recrCode}'";
                DBHelper.ExecuteNonQuery(sql);
            }

            /// <summary>
            /// 获取该销售记录明细获取的利润
            /// </summary>
            /// <param name="salesID"></param>
            /// <param name="goodsID"></param>
            /// <returns></returns>
            private static double GetProfit(string sdid)
            {
                string sql = "select\n" +
                    "((sd.AloneAmount-gs.StorePrice)*sd.Quantity) Pro\n" +
                    "from [dbo].[SalesDetail] sd\n" +
                    "join [dbo].[Goods] gs on gs.GoodsID=sd.GoodsID\n" +
                    $"where sd.SDID={sdid}";
                double result = double.Parse(DBHelper.ExecuteScalar(sql).ToString());
                return result;
            }
        }
    }
}
